ajCache function
Available since AlchemyJ v4.1
Description
The ajCache function is used for efficient storage and retrieval of computationally intensive or repetitive data to avoid repeated calculations. The function transforms array formula into individual cell formula.
Syntax
ajCache(cache_name, row_index, col_index, [depending_cell], [in_value])
| Argument Name | Argument Type | Description |
|---|---|---|
| cache_name (required) | String | The name of the cache. |
| row_index (required) | Double | Current row index of the range. |
| col_index (required) | Double | Current column index of the range. |
| depending_cell (optional) | Range | The cell reference of the range. |
| in_value (optional) | Variant | The original array formula. |
The function will return:
1) Return Value: Value of the referenced cell
2) Return Type: Single Value/ Multiple values (array formula)
Example
Here are some examples of using the ajCache function.
Example - Get cell value from cache
Assume it has some input parameters need to do the validation, use the ajCache to store the validation result with run the formula one time, then get the validation result from cache cell by cell
Setting the validation rule in the DataDictionary sheet
When only use the ajCheckDataItem, need to set a formula range, the formula will run cell by cell
But use the ajCache only run the formula one time, then get the cell data from the cache.
Note: This ajCache is just for support return type is Multiple values (array formula) function.
Click here to download the use case workbooks for further reference.
Error Scenarios
It will return #VALUE! when missing any required parameter or mismatch parameter type. Besides, system will raise error for below scenario(s).
| Error Scenario |
|---|
| row_index must be a number |
| column_index must be a number |